package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;

import com.LostAndFound.domain.Article;
import com.LostAndFound.domain.FoundArticle;
import com.LostAndFound.utils.DataSourceUtils;

public class MyIssueDao {
	
	public List<Article> getArticleOrderList(String uid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from article where uid = ? order by issuetime desc";
		return runner.query(sql, new BeanListHandler<Article>(Article.class),uid);
	}

	public Map<String, Object> showMyIssueArticleDetail(String aid, String foundFlag, String invalidFlag) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = null;
		Map<String, Object> map = null;
		if("0".equals(foundFlag) && "1".equals(invalidFlag)) {
			sql = " select a.*,c.cname,f.foundtime,f.foundinfo "
					+ " from article a,category c,foundarticle f "
					+ " where a.cid = c.cid and foundflag = 0 and invalidflag = 1 and a.aid = ? "
					+ " and f.aid=a.aid ";
			map = runner.query(sql, new MapHandler(), aid);
		}else {
			sql = " select a.*,c.cname from article a,category c "
					+ " where a.cid = c.cid and foundflag = ? and invalidflag = ? and a.aid = ? ";
			map = runner.query(sql, new MapHandler(), foundFlag, invalidFlag, aid);
		}
		/*else if("1".equals(foundFlag) && "1".equals(invalidFlag)){
			sql = " select a.*,c.cname from article a,category c "
					+ " where a.cid = c.cid and foundflag = 1 and invalidflag = 1 and a.aid = ? ";
		}else  if("1".equals(foundFlag) && "0".equals(invalidFlag)){
			sql = " select a.*,c.cname from article a,category c "
					+ " where a.cid = c.cid and foundflag = 1 and invalidflag = 0 and a.aid = ?";
		}else  if("2".equals(foundFlag) && "2".equals(invalidFlag)){
			sql = " select a.*,c.cname from article a,category c "
					+ " where a.cid = c.cid and foundflag = 2 and invalidflag = 2 and a.aid = ?";
		}else  if("4".equals(foundFlag) && "4".equals(invalidFlag)){
			sql = " select a.*,c.cname from article a,category c "
					+ " where a.cid = c.cid and foundflag = 4 and invalidflag = 4 and a.aid = ?";
		}
		return runner.query(sql, new MapHandler(),aid);*/
		return map;
	}

	public void handleAddFoundArticle(FoundArticle foundArticle) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = null;
		sql = "insert into foundarticle(aid,foundtime,foundinfo) values(?,?,?)";
		Object[] params = {foundArticle.getArticle().getAid(),foundArticle.getFoundTime(),foundArticle.getFoundInfo()};
		runner.update(sql, params);
	}

	public void handleUpdateFoundFlag(FoundArticle foundArticle) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update article set foundflag = 0 where aid = ?";
		runner.update(sql, foundArticle.getArticle().getAid());		
	}

	public void handleDeleteArticleInFoundArticle(String aid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from foundarticle where aid = ?";
		runner.update(sql,aid);
	}

	public void handleDeleteArticleInArticle(String aid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "delete from article where aid = ?";
		runner.update(sql, aid);
	}

}
